const crypto = require('crypto')
const hash = (str) => {
    let h = crypto.createHash('sha256')
    h.update(str)
    return h.digest('hex')
}
class DAO {
    constructor(K) {
        this.conn = K
    }

    async verify(name, passwd) {
        try {
            let [{ password, salt }] = await this.conn('users')
                .join('user_type', 'users.type', '=', 'user_type.id')
                .select('password', 'salt')
                .where({
                    'users.name': name,
                    'user_type.name': '系统用户'
                })
            return hash(salt + passwd) === password
        } catch (e) {
            // Cannot destructure property `password` of 'undefined' or 'null'
            return false
        }
    }

    async users() {
        return await this.conn('users').select('name')
    }


    async newUser(name, newName, passwd, alias) {
        let target = await this.conn('users').select('id').where({ name: newName })
        if (target.length > 0) throw new Error('该用户已存在') //TODO: 不允许注册已存在用户
        let type = this.conn('user_type').select('id').where('name', '系统用户')
        let salt = hash(Math.random().toString())
        let password = hash(salt + passwd)
        return await this.conn('users')
            .where({ name })
            .update({
                type,
                name: newName,
                alias,
                salt,
                password
            }, 'id')
    }

    async newGuest(name, fp, attrs = {}) {
        try {
            let id = await this.conn.raw(`
            WITH ut AS ( SELECT id FROM user_type WHERE name = '临时用户' )
              INSERT INTO users (type, name, fingerprint, attrs)
                SELECT ut.id, ?, ?, ? FROM ut
                WHERE NOT EXISTS (SELECT id FROM users WHERE name = ? AND type = ut.id)
              RETURNING id;
            `, [name, fp, attrs, name])
            return id.rows.map(x => x.id)
        } catch (e) {
            console.log(e)
        }
    }
    //async new_msg()
    async permissions(user) {
        let permission = await this.conn.raw(`
          SELECT permission.name, permission.description FROM users
          JOIN user_role ON users.id = user_role.uid
          JOIN roles ON user_role.rid = roles.id
          JOIN role_permission ON roles.id = role_permission.rid
          JOIN permission ON role_permission.pid = permission.id
          WHERE users.name = ?
        `, [user])
        return permission.rows.map(x => x.name)
    }
    async userInfo({ name, fp }) {
        let query = this.conn('users')
            .join('user_type', 'users.type', 'user_type.id')
            .select('users.alias', 'users.name AS name',
            'user_type.name AS type', 'users.attrs',
            'users.avatar', 'users.fingerprint')
        let sql = name
            ? query.where({ 'users.name': name })
            : query.where({ 'users.fingerprint': fp })
        let r = (await sql)[0]
        return r || {}
    }
    async userSetInfo(name, info) {
        return await this.conn('users')
            .update(info)
            .where({ name })
    }
    async newMessage(target, user, content) {
        let src = this.conn('users').select('id').where({ name: user })
        let dist = this.conn('users').select('id').where({ name: target })
        await this.conn('msg_user').insert({ dist, src, content })
    }

    async rooms() {
        return await this.conn('room').select(['name', 'public'])
    }
    async newRoomMsg(room, user, content) {
        let src = this.conn('users').select('id').where({ name: user })
        let dist = this.conn('room').select('id').where({ name: room })
        await this.conn('msg_room').insert({ dist, src, content })
    }
    async roomHistory(room, start, end) {
        let E = end || new Date(), S = start || new Date('2000-1-1')
        let R = this.conn('room').select('id').where({ name: room })
        return await this.conn('msg_room')
            .select('created_time', 'src', 'content')
            .where('created_time', '>=', S)
            .andWhere('created_time', "<=", E)
            .andWhere('dist', R)
    }
    async newRoom(room, user, pub) {
        await this.conn.transaction(async trx => {
            try {
                let [rid] = await trx
                    .insert({ name: room, public: !!pub }, 'id')
                    .into('room')
                let [uid] = await trx.select('id')
                    .from('users')
                    .where({ name: user })
                    .then(x => x.map(i => i.id))
                await trx.into('user_room')
                    .insert({ uid, rid })
                let oid = await trx.from('roles').select('id')
                    .whereIn('name', ['管理员', '主持人'])
                    .then(x => x.map(i => i.id))
                await trx.into('user_room_role')
                    .insert(oid.map(o => {
                        return {
                            uid,
                            rid,
                            oid: o
                        }
                    }))
            } catch (e) {
                console.log(e)
                throw e
            }
        })
    }
    async delRoom(room, user) {

    }
    async joinRoom(room, user) {
        return await this.conn.raw(`
          INSERT INTO user_room (uid, rid)
          VALUES (
            (SELECT id FROM users WHERE name = ?),
            (SELECT id FROM room WHERE name = ?)
          ) ON CONFLICT (uid, rid)
            DO UPDATE SET join_time = current_timestamp, leave_time = NULL
        `, [user, room])
    }
    async leaveRoom(room, user) {
        let rid = await this.conn('room').select('id')
            .where({ name: room })
        let uid = this.conn('users').select('id')
            .where({ name: user })
        await this.conn('user_room').update({ 'leave_time': new Date() })
            .where({ uid, rid })
    }
    async roomsOfUser(user) {
        return await this.conn('user_room')
            .select('room.name')
            .join('room', 'user_room.rid', 'room.id')
            .join('users', 'user_room.uid', 'users.id')
            .where('users.name', user)
            .then(x => x.map(x1 => x1.name))
    }

}

module.exports = DAO
